Triggers are pre-compiled SQL statements that are executed by the server whenever the user attempts to modify the data in the specified table. The user cannot explicitly execute triggers. SQL Server executes the trigger automatically immediately after the data modification statements are executed.

The behavior of a trigger is just like a transaction. A trigger fails whenever any part of the trigger is dissatisfied by the data supplied by the user. In such a case the data changes made by the user are not reflected in the specified table.

While defining a trigger, certain points have to be kept in mind:

•       Parameters cannot be passed with triggers.
•       Triggers names must be unique within a database.
•       Triggers cannot be created on views or temporary tables.
•      Triggers are used to maintain data integrity.
•      Triggers do not return any return sets to the user.

Syntax:

CREATE TRIGGER trigger_name
ON table_name
[FOR | AFTER | INSTEAD OF] {INSERT | UPDATE | DELETE}
AS statement
 
where
·         trigger_name : Name of the trigger.
·         Table_name : Name of the table on which trigger is created.

Example

CREATETRIGGER t1
ON EMP
FORINSERT
ASraiserror('%d rows have been modified', 0, 1, @@rowcount)

 

To see the effect of trigger, insert a new row to the table.

insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

Output

1 rows have been modified

(1 row(s) affected) 

Deletion of Triggers

The DROP TRIGGER statement can be used to delete triggers.

Syntax:

DROP TRIGGER trigger_name

DROP TRIGGER t1 

Types of Triggers

Basically triggers are classified into two main types :

1.       After Trigger

2.      Instead Of Trigger
<![if !supportLineBreakNewLine]>

After Trigger

These triggers run after an insert, update or delete on a table. 

After Triggers can be further classified into three types: 

1.       After Insert Trigger

2.       After Update Trigger

3.       After Delete Trigger 

After Insert Trigger

This trigger is fired after an INSERT on the table.

Example

CREATE TRIGGER Insert_Trigger
ON EMP
AFTER INSERT
AS PRINT 'INSERT TRIGGER EXECUTED AFTER INSERT QUERY'
  
After creating trigger, insert the row in the table and see the result
insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values('5','Avadesh','Patel','IT','8000')

Output

INSERT TRIGGER EXECUTED AFTER INSERT QUERY

 

(1 row(s) affected)

 

After Update Trigger

This trigger is fired after an UPDATE on the table.

Example

CREATE TRIGGER Update_Trigger
ON EMP
AFTER UPDATE
AS PRINT 'UPDATE TRIGGER EXECUTED AFTER UPDATE QUERY'

 

After creating trigger, update the row in the table and see the result

update EMP  set EMPFIRSTNAME='Smith', EMPLASTNAME='Johnson' where EMPID=5

Output

UPDATE TRIGGER EXECUTED AFTER UPDATE QUERY

 

(1 row(s) affected)

After Delete Trigger

This trigger is fired after a DELETE on the table.

Example

CREATE TRIGGER Delete_Trigger
ON EMP
AFTER DELETE
AS PRINT 'DELETE TRIGGER EXECUTED AFTER DELETE QUERY'

 

After creating trigger, delete the row in the table and see the result

delete from EMP where EMPID=5

Output

DELETE TRIGGER EXECUTED AFTER DELETE QUERY

 

(1 row(s) affected)

Instead Of Trigger

INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

Syntax:

CREATE TRIGGER trigger_name
ON table_name
AS
BEGIN
<SOL Statements>
END

 

Instead Of Trigger can be classified further into three types:-

1.       Instead Of Insert Trigger

2.       Instead Of Update Trigger

3.       Instead Of Delete Trigger

Instead Of Insert Trigger 

Example

First create a table

CREATE TABLE EMP_TEST_AUDIT
(
 EMPID int,
 EMPFIRSTNAME varchar(50),
 EMPLASTNAME varchar(50),
 DEPT varchar(20),
 SALARY int,
 AUDIT_ACTION varchar(100),
 AUDIT_TIME datetime
)

Create Instead Of Insert Trigger

CREATE TRIGGER INSTEADOF_TRIGGER
ON EMP
INSTEAD OF INSERT
AS
       declare @EMPID int;
       declare @EMPFIRSTNAME  varchar(50);
       declare @EMPLASTNAME  varchar(50);
       declare @DEPT varchar(20);
       declare @SALARY int;
      
       select @EMPID=i.EMPID from inserted i;
       select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;
       select @EMPLASTNAME=i.EMPLASTNAME from inserted i;
       select @DEPT=i.DEPT from inserted i;
       select @SALARY=i.SALARY from inserted i;
      
BEGIN
       insert into EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY)
       insert into EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME) values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());
       PRINT'RECORD INSERTED'
END

Insert a row in the table

insertinto EMP(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY)values('5','Avadesh','Patel','IT','8000')

Output

RECORD INSERTED

 

(1 row(s) affected)

 

Now you will see that record is also inserted in EMP_TEST_AUDIT table

Inserted Of Delete Trigger 

Example

CREATETRIGGER INSTEADOF_DELETETRIGGER
ON EMP
INSTEADOFDELETE
AS
      declare @EMPID int;
      declare @EMPFIRSTNAME  varchar(50);
      declare @EMPLASTNAME  varchar(50);
      declare @DEPT varchar(20);
      declare @SALARY int;
      
      select @EMPID=d.EMPID from deleted d;
      select @EMPFIRSTNAME=d.EMPFIRSTNAME from deleted d;
      select @EMPLASTNAME=d.EMPLASTNAME from deleted d;
      select @DEPT=d.DEPT from deleted d;
      select @SALARY=d.SALARY from deleted d;
      
BEGIN
      deletefrom EMP where EMPID=@EMPID
      insertinto EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME)values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'DELETED-INSTEAD OF DELETE TRIGGER',getdate());
      PRINT'RECORD DELETED'
END

 

 

Delete a record from the table.

deletefrom Emp where EMPID=5

Output

RECORD DELETED

 

(1 row(s) affected)

 

 

 Instead of Update Trigger 

Example

CREATETRIGGER INSTEADOF_UPDATETRIGGER
ON EMP
INSTEADOFUPDATE
AS
      declare @EMPID int;
      declare @EMPFIRSTNAME  varchar(50);
      declare @EMPLASTNAME  varchar(50);
      declare @DEPT varchar(20);
      declare @SALARY int;
      
      select @EMPID=i.EMPID from inserted i;
      select @EMPFIRSTNAME=i.EMPFIRSTNAME from inserted i;
      select @EMPLASTNAME=i.EMPLASTNAME from inserted i;
      select @DEPT=i.DEPT from inserted i;
      select @SALARY=i.SALARY from inserted i;
      
BEGIN
      update EMP set EMPFIRSTNAME=@EMPFIRSTNAME, EMPLASTNAME=@EMPLASTNAME where EMPID=@EMPID
      insertinto EMP_TEST_AUDIT(EMPID,EMPFIRSTNAME,EMPLASTNAME,DEPT,SALARY,AUDIT_ACTION,AUDIT_TIME)values(@EMPID,@EMPFIRSTNAME,@EMPLASTNAME,@DEPT,@SALARY,'INSERTED-INSTEAD OF TRIGGER',getdate());
      PRINT'RECORD UPDATED'
END

 

Update the record in the table 

update EMP set EMPFIRSTNAME='Smith',EMPLASTNAME='Johnson'where EMPID=4 

Output

RECORD UPDATED

 

(1 row(s) affected)

You can also read these related post

https://www.mindstick.com/Articles/330/triggers-in-sql-server

https://www.mindstick.com/Articles/569/trigger-in-sql-server